﻿/*
'Description:检验周期统计看板(生产作业会)中间表结转
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2022-11-10
'Rewriter:
'Rewrite Date:
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using UFC.Common;
using UFC.DBUtility;
using System.Collections;
using System.Data;
using System.Configuration;

namespace TransferMesReportForm
{
    //检验周期统计看板(生产作业会)
    class TransferCheckCyclebBoard
    {
        private static string mesReportConn = ConfigurationManager.AppSettings["MesReportOracleConn"];
        //入口
        public void RunMain()
        {
            LogHelp.WriteLog("TransferCheckCyclebBoard服务循环开始---------------------");

            System.Threading.Thread thAddData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    AddData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferCheckCyclebBoard服务-添加数据", e);
                }
            }));
            thAddData.Start();

            System.Threading.Thread thUpdateData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    UpdateData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferCheckCyclebBoard服务-更新数据", e);
                }
            }));
            thUpdateData.Start();

            System.Threading.Thread thdeleteData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    DeleteData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferCheckCyclebBoard服务-删除数据", e);
                }
            }));
            thdeleteData.Start();

            //删除中间表r_checkcycleboard【批次被删除】数据
            System.Threading.Thread thDeleteWithContainerBeDeleted = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    DeleteWithContainerBeDeleted();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferCheckCyclebBoard服务-删除中间表r_checkcycleboard【批次被删除】数据", e);
                }
            }));
            thDeleteWithContainerBeDeleted.Start();

            //更新检验待检备注
            System.Threading.Thread thUpdateCheckNotes = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    UpdateCheckNotes();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferCheckCyclebBoard服务-更新中间表r_checkcycleboard【检验待检备注】数据", e);
                }
            }));
            thUpdateCheckNotes.Start();

            //删除批次已关闭数据
            System.Threading.Thread thDeleteClosedContainer = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    deleteClosedContainerData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferCheckCyclebBoard服务-删除中间表r_checkcycleboard【批次已关闭】数据", e);
                }
            }));
            thDeleteClosedContainer.Start();

            //更新入库状态
            System.Threading.Thread thUpdateIsInstore = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    updateIsInstore();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferCheckCyclebBoard服务-更新中间表r_checkcycleboard【入库状态】数据", e);
                }
            }));
            thUpdateIsInstore.Start();

            //线程等待
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thAddData.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thUpdateData.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thdeleteData.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thDeleteWithContainerBeDeleted.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thUpdateCheckNotes.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thDeleteClosedContainer.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thUpdateIsInstore.ThreadState != System.Threading.ThreadState.Stopped);

            LogHelp.WriteLog("TransferCheckCyclebBoard服务循循环结束---------------------");
        }

        //增量数据
        public void AddData()
        {
            string strSql = @"
SELECT csr.containername,csr.workflowstepname,fa.factoryid,fa.factoryname,f.factorycode,
       cci.fromworkcentername,cci.mfgordername,cci.familylistnames,cci.productname,cci.productdescription PRODUCTDESC,
       csr.reportdate,csr.reportemployeename REPORTER,csr.checkdate,csr.checkemployeename CHECKER,cu.notes CHECKSUSPENDNOTES,csr.lossqty,csr.passqty+NVL(csr.defectqty,0)+NVL(csr.reworkqty,0) passqty,csr.containersteprecordid,
       s.lastcheck,s.factoryid specfactoryid,'' status,'' specchecktype,csr.containerid,csr.workflowstepid
FROM (
    SELECT csr.* FROM containersteprecord csr 
    WHERE NVL2(csr.containersteprecordid,'1','0')='1' AND csr.reportdate >=SYSDATE-5  
) csr
INNER JOIN containercurrentinfo cci ON cci.containerid=csr.containerid
LEFT JOIN factory fa ON fa.factoryid=cci.originalfactoryid--主制车间
LEFT JOIN mes_factory_mapping f ON f.factoryid = fa.factoryid
LEFT JOIN containerchecksuspending cu ON cu.containerid=csr.containerid AND cu.workflowstepid=csr.workflowstepid AND cu.isuse=0--待处理
LEFT JOIN spec s ON s.specid=csr.specid
LEFT JOIN r_checkcycleboard@mesreport rc ON rc.containersteprecordid=csr.containersteprecordid
WHERE NVL2(rc.containersteprecordid,'1','0')='0' AND ROWNUM<5000";
            var dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var insertSqlList = new ArrayList();
                var insertSql = @"INSERT INTO r_checkcycleboard rc  
(rc.factoryname,rc.factorycode,rc.containername,rc.workflowstepname,rc.fromworkcentername,rc.mfgordername,
rc.familylistnames,rc.productname,rc.productdesc,
rc.checker,rc.checksuspendnotes,rc.REPORTER,rc.reportdate,rc.checkdate,rc.status,rc.lossqty,rc.passqty,rc.containersteprecordid,rc.SPECCHECKTYPE,rc.containerid,rc.workflowstepid) 
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',
to_date('{12}','yyyy/mm/dd hh24:mi:ss'),to_date('{13}','yyyy/mm/dd hh24:mi:ss'),'{14}','{15}','{16}','{17}','{18}','{19}','{20}')";
                foreach (DataRow dr in dt.Rows)
                {
                    if (!string.IsNullOrWhiteSpace(dr["checkdate"].ToString()))
                    {
                        dr["status"] = "2";
                    }
                    else
                    {
                        dr["status"] = "1";
                    }

                    //工序类型：1->普通工序 2->检验工序（不包括转工序） 3->转工检验工序（既是检验序又是转工序） 4->转工序（不包括检序）
                    if (dr["lastcheck"].ToString() == "1")
                    {
                        if (dr["specfactoryid"].ToString() == dr["factoryid"].ToString())
                        {
                            dr["specchecktype"] = "2";//检验工序（不包括转工序）
                        }
                        else
                        {
                            dr["specchecktype"] = "3";//转工检验工序（既是检验序又是转工序）
                        }
                    }
                    else
                    {
                        if (dr["specfactoryid"].ToString() == dr["factoryid"].ToString())
                        {
                            dr["specchecktype"] = "1";//普通工序
                        }
                        else
                        {
                            dr["specchecktype"] = "4"; //转工序（不包括检序）
                        }
                    }

                    insertSqlList.Add(string.Format(insertSql, dr["factoryname"].ToString(), dr["factorycode"].ToString(), dr["containername"].ToString(),
                        dr["workflowstepname"].ToString(), dr["fromworkcentername"].ToString(), dr["mfgordername"].ToString(),
                        dr["familylistnames"].ToString(), dr["productname"].ToString(),
                        dr["productdesc"].ToString(), dr["checker"].ToString(), dr["checksuspendnotes"].ToString(), dr["REPORTER"].ToString(),
                        dr["reportdate"].ToString(), dr["checkdate"].ToString(),
                        dr["status"].ToString(), dr["lossqty"].ToString(), dr["passqty"].ToString(), dr["containersteprecordid"].ToString(),
                        dr["specchecktype"].ToString(), dr["containerid"].ToString(), dr["workflowstepid"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(insertSqlList, mesReportConn);
                LogHelp.WriteLog("TransferCheckCyclebBoard服务-添加中间表r_checkcycleboard数据条数：" + insertSqlList.Count);
            }
        }

        //删除数据
        public void DeleteData()
        {
            string strSql = @"
SELECT rc.containersteprecordid 
FROM r_checkcycleboard@mesreport rc
LEFT JOIN containersteprecord csw ON rc.containersteprecordid=csw.containersteprecordid
WHERE csw.containerid IS NULL";
            var dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var delSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    delSqlList.Add(string.Format("delete r_checkcycleboard where containersteprecordid='{0}'", dr["containersteprecordid"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(delSqlList, mesReportConn);
                LogHelp.WriteLog("TransferCheckCyclebBoard服务-删除中间表r_checkcycleboard数据条数：" + delSqlList.Count);
            }
        }

        //更新数据,更新检验信息
        public void UpdateData()
        {
            string strSql = @"
SELECT csr.checkdate,csr.checkemployeename checker,cu.notes checksuspendnotes,2 status,csr.lossqty,
       csr.passqty+nvl(csr.defectqty,0)+nvl(csr.reworkqty,0) passqty,
       rc.containersteprecordid 
FROM containersteprecord csr 
INNER JOIN r_checkcycleboard@mesreport rc ON csr.containersteprecordid=rc.containersteprecordid  
LEFT JOIN containerchecksuspending cu ON cu.containerid=csr.containerid AND cu.workflowstepid=csr.workflowstepid AND cu.isuse=0--待处理
WHERE (csr.checkdate>=SYSDATE-90 OR cu.notes<>NVL(rc.checksuspendnotes,'0'))  AND rc.status=1  
      AND ROWNUM<5000

UNION ALL
-----检验撤销

SELECT null checkdate,'' checker,cu.notes checksuspendnotes,1 status,null lossqty,null passqty,
       cci.containerstepworkreportid containersteprecordid  
FROM (

  SELECT cfci.containerid,cfci.workflowstepid,cfci.createdate checkdate,cfci.containerfinishedcheckinfoid containercheckinfoid,
         csw.containerstepworkreportid FROM containerfinishedcheckinfo cfci 
  INNER JOIN containerstepworkreport csw ON csw.containerfinishedcheckinfoid=cfci.containerfinishedcheckinfoid
  WHERE cfci.createdate>=SYSDATE-365 AND cfci.isuse=1

  UNION ALL

  SELECT cci.containerid,cci.workflowstepid,cci.createdate checkdate,cci.containercheckinfoid,cci.containerstepworkreportid 
  FROM containercheckinfo cci 
  WHERE cci.createdate>=SYSDATE-365  AND cci.isuse=1
) cci
INNER JOIN r_checkcycleboard@mesreport rc ON rc.containersteprecordid =cci.containerstepworkreportid AND rc.checkdate=cci.checkdate
LEFT JOIN containerchecksuspending cu ON cu.containerid=cci.containerid AND cu.workflowstepid=cci.workflowstepid AND cu.isuse=0--待处理
";
            var dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var updateSqlList = new ArrayList();

                foreach (DataRow dr in dt.Rows)
                {
                    string status = string.IsNullOrWhiteSpace(dr["checkdate"].ToString()) ? "1" : "2";
                    updateSqlList.Add(string.Format(@"
UPDATE r_checkcycleboard rc SET rc.checker='{0}',rc.checksuspendnotes='{1}',rc.checkdate=to_date('{2}','yyyy/mm/dd hh24:mi:ss'),rc.status='{3}',rc.lossqty='{4}',rc.passqty='{5}'
WHERE rc.containersteprecordid='{6}'", dr["checker"].ToString(),
                                           dr["checksuspendnotes"].ToString(),
                                           dr["checkdate"].ToString(),
                                           status,
                                           dr["lossqty"].ToString(),
                                           dr["passqty"].ToString(),
                                           dr["containersteprecordid"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(updateSqlList, mesReportConn);
                LogHelp.WriteLog("TransferCheckCyclebBoard服务-更新中间表r_checkcycleboard数据条数：" + updateSqlList.Count);
            }
        }

        //删除批次被删除报工数据
        public void DeleteWithContainerBeDeleted()
        {
            string strSql = @"
SELECT t.containersteprecordid
FROM r_checkcycleboard t
LEFT JOIN containercurrentinfo@insitedb74 cci ON cci.containername = t.containername
WHERE cci.containerid IS NULL";
            var dt = OracleHelper.Query(strSql, mesReportConn).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var delSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    delSqlList.Add(string.Format("delete r_checkcycleboard where containersteprecordid='{0}'", dr["containersteprecordid"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(delSqlList, mesReportConn);
                LogHelp.WriteLog("TransferCheckCyclebBoard服务-删除中间表r_checkcycleboard【批次被删除】数据条数：" + delSqlList.Count);
            }
        }

        //更新检验备注
        public void UpdateCheckNotes()
        {
            DataTable dtCheckNotes = OracleHelper.Query("SELECT * FROM containerchecknotes t").Tables[0];
            if (dtCheckNotes.Rows.Count > 0)
            {
                foreach (DataRow dr in dtCheckNotes.Rows)
                {
                    string sql = "SELECT t.containersteprecordid FROM r_checkcycleboard t WHERE t.containername = '{0}' AND t.workflowstepname = '{1}'";
                    sql = string.Format(sql, dr["containername"].ToString(), dr["workflowstepname"].ToString());
                    DataTable dt = OracleHelper.Query(sql, mesReportConn).Tables[0];
                    if (dt.Rows.Count > 0)
                    {
                        string updateSql = "UPDATE r_checkcycleboard t SET t.checksuspendnotes = '{0}' WHERE t.containersteprecordid = '{1}'";
                        updateSql = string.Format(updateSql, dr["notes"].ToString(), dt.Rows[0]["containersteprecordid"]);
                        int i = OracleHelper.ExecuteSql(updateSql, mesReportConn);
                        if (i > 0)
                        {
                            string deleteSql = string.Format("DELETE FROM containerchecknotes WHERE id = '{0}'", dr["id"].ToString());
                            OracleHelper.ExecuteSql(deleteSql);
                        }
                    }
                }
                LogHelp.WriteLog("TransferCheckCyclebBoard服务-更新中间表r_checkcycleboard【检验待检备注】数据条数：" + dtCheckNotes.Rows.Count);
            }
        }

        //删除批次已关闭数据
        public void deleteClosedContainerData()
        {
            string sql = @"
SELECT t.containersteprecordid,t.id
FROM r_checkcycleboard t
INNER JOIN containercurrentinfo@insitedb74 cci ON t.containername = cci.containername
WHERE cci.containerstatus = 2";
            DataTable dtClosedContainer = OracleHelper.Query(sql, mesReportConn).Tables[0];
            if (dtClosedContainer.Rows.Count > 0)
            {
                foreach (DataRow dr in dtClosedContainer.Rows)
                {
                    string delSql = string.Format("DELETE r_checkcycleboard t WHERE t.id = '{0}'", dr["id"].ToString());
                    OracleHelper.ExecuteSql(delSql, mesReportConn);
                }
                LogHelp.WriteLog("TransferCheckCyclebBoard服务-删除中间表r_checkcycleboard【批次已关闭】数据条数：" + dtClosedContainer.Rows.Count);
            }
        }

        //更新入库状态
        public void updateIsInstore()
        {
            string sql = @"
SELECT t.containersteprecordid,t.id,cci.isinstore
FROM r_checkcycleboard t
INNER JOIN containercurrentinfo@insitedb74 cci ON t.containername = cci.containername
WHERE (cci.isinstore = 1 AND t.isinstore <> 1) OR (cci.isinstore = -1 AND t.isinstore <> -1)";
            DataTable dtClosedContainer = OracleHelper.Query(sql, mesReportConn).Tables[0];
            if (dtClosedContainer.Rows.Count > 0)
            {
                foreach (DataRow dr in dtClosedContainer.Rows)
                {
                    string delSql = string.Format("UPDATE r_checkcycleboard t SET t.isinstore = '{0}' WHERE t.id = '{1}'",
                        dr["isinstore"].ToString(), dr["id"].ToString());
                    OracleHelper.ExecuteSql(delSql, mesReportConn);
                }
                LogHelp.WriteLog("TransferCheckCyclebBoard服务-更新中间表r_checkcycleboard【入库状态】数据条数：" + dtClosedContainer.Rows.Count);
            }
        }
    }
}